"""
db_tool.py:
    操作 Mysql数据库(初始化、连接建立、关闭、配置等)
"""
import os
from dotenv import load_dotenv
import mysql.connector
import logging
from typing import List, Any

# 加载环境变量配置
load_dotenv()

# 定义日志
logging.basicConfig(level=logging.INFO)  # 设定日志级别
logger = logging.getLogger(__file__)  # 拿到当前文件的日志对象


# 定义数据库连接对象
class DatabaseConnection:
    def __init__(self):
        """初始化 mysql数据库相关"""
        # self.host = os.getenv("MYSQL_HOST","localhost")
        self.host = "8.136.9.202"
        self.port = "3306"
        self.user = "root"
        self.password = os.getenv("REMOTE_MYSQL_PASSWORD")
        self.database = "menu"

        self.connection = None  # 数据库连接对象
        self.cursor = None  # 游标对象 (真正执行 SQL语句的对象)

    def connect(self) -> bool:
        """建立数据库连接"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset="utf8mb4"
            )

            if self.connection.is_connected():
                self.cursor = self.connection.cursor(dictionary=True)  # 返回字典结构参数，反之，返回元组
                return True
            else:
                logger.error("数据库连接已经关闭: 游标获取失败")
                return False

        except Exception as error:
            logger.error(f"数据库连接建立失败: {str(error)}")
            return False

    def __enter__(self):
        """上下文管理器对象入口"""
        try:
            if self.connect():
                return self  # 当前实例对象返回
        except Exception as error:
            raise Exception("数据库连接失败: " + error)

    def __exit__(self, exc_type, exc_val, exc_tb):
        """上下文管理器对象出口"""
        self.disconnect()

    def disconnect(self):
        """关闭数据库连接"""
        try:
            self.cursor.close()
            if self.connection and self.connection.is_connected():
                self.connection.close()
        except Exception as error:
            logger.error(f"数据库关闭失败: {str(error)}")


# 获取所有菜单项
def get_all_menu_items() -> str:
    """获取 menu_items表中的所有菜品，然后将单个菜品转换成字符串并拼接"""
    try:
        with DatabaseConnection() as db:

            # 1、定义查询 SQL
            query = """
                select id, # 菜品 id 
                       dish_name, # 菜名
                       price, # 价格
                       description, # 描述
                       category, # 分类
                       spice_level, # 辣度等级
                       flavor, # 口味
                       main_ingredients, # 主要食材
                       cooking_method, # 烹饪方式
                       is_vegetarian, # 素食
                       allergens, # 过敏原信息
                       is_available, # 是否可供应
                       created_at, # 创建时间
                       updated_at # 更新时间
                from menu_items
                where is_available = 1
                order by category, dish_name
            """

            # 2、获取游标对象
            db.cursor.execute(query)
            menu_items = db.cursor.fetchall()

            if not menu_items:
                return "当前没有"

            menu_item_str_list = []
            # 遍历菜单项
            for item in menu_items:
                # 处理辣度级别
                spice_level_mapping = {0: "不辣", 1: "微辣", 2: "中辣", 3: "重辣"}
                spice_text = spice_level_mapping.get(item["spice_level"], "未知")
                # 处理素食
                vegetarian_text = "是" if item["is_vegetarian"] else "否"
                # 过敏原处理
                allergens_text = item["allergens"] if item["allergens"] and item["allergens"].strip() else "未知"
                # 格式化单条菜品信息
                menu_text = f"菜品ID:{item['id']}|菜品名称:{item['dish_name']}|价格:¥{item['price']:.2f}|分类:{item['category']}|辣度:{spice_text}|素食:{vegetarian_text}|口味:{item['flavor'] or '暂无描述'}|烹饪方法:{item['cooking_method'] or '暂无'}|主要食材:{item['main_ingredients'] or '暂无'}|菜品描述:{item['description'] or '暂无描述'}|过敏原:{allergens_text}"
                menu_item_str_list.append(menu_text)

            menu_formated_str = "\n".join(menu_item_str_list)
            return menu_formated_str

    except Exception as e:
        logger.error(f"查询菜品列表信息失败: {str(e)}")
        return "查询失败"


# 查询菜品列表字典接口
def get_menu_items_list() -> List[dict]:
    """查询菜品列表"""
    try:
        with DatabaseConnection() as db:
            query = """
                    select id, # 菜品 id 
                           dish_name, # 菜名
                           price, # 价格
                           description, # 描述
                           category, # 分类
                           spice_level, # 辣度等级
                           flavor, # 口味
                           main_ingredients, # 主要食材
                           cooking_method, # 烹饪方式
                           is_vegetarian, # 素食
                           allergens, # 过敏原信息
                           is_available, # 是否可供应
                           created_at, # 创建时间
                           updated_at # 更新时间
                    from menu_items
                    where is_available = 1
                    order by category, dish_name
                """
            db.cursor.execute(query)
            item_list = db.cursor.fetchall()
            if not item_list:
                return []

            processed_item_list = []
            for item in item_list:
                # 辣度等级转换
                spice_levels = {0: "不辣", 1: "微辣", 2: "中辣", 3: "重辣"}
                spice_text = spice_levels.get(item['spice_level'], "未知")
                # 处理数据，确保字段完整性
                processed_item = {
                    "id": item['id'],
                    "dish_name": item['dish_name'],
                    "price": float(item['price']),
                    "formatted_price": f"¥{item['price']:.2f}",
                    "description": item['description'] or "暂无描述",
                    "category": item['category'],
                    "spice_level": item['spice_level'],
                    "spice_text": spice_text,
                    "flavor": item['flavor'] or "暂无描述",
                    "main_ingredients": item['main_ingredients'] or "暂无",
                    "cooking_method": item['cooking_method'] or "暂无",
                    "is_vegetarian": bool(item['is_vegetarian']),
                    "vegetarian_text": "是" if item['is_vegetarian'] else "否",
                    "allergens": item['allergens'] if item['allergens'] and item['allergens'].strip() else "无",
                    "is_available": bool(item['is_available'])
                }
                processed_item_list.append(processed_item)
            return processed_item_list

    except Exception as error:
        logger.error(f"查询菜品列表转换结构化字典失败: {str(error)}")
        return []


# 查询菜品信息根据分类
def get_menu_items_list_by_category() -> dict[str, Any]:
    """定义查询菜品信息更具分类"""

    try:
        menu_items = get_menu_items_list()
        if not menu_items:
            return {}

        categories = {}
        for item in menu_items:
            category = item['category']
            if category not in categories:
                categories[category] = []
            categories[category].append(item)
        return categories
    except Exception as e:
        logger.error(f"根据菜品分类查询菜品列表: {str(e)}")
        return {}


def get_menu_item_by_id(item_id: int) -> dict:
    """
    根据菜品ID获取单个菜品信息

    Args:
        item_id (int): 菜品ID

    Returns:
        dict: 菜品信息字典，如果未找到返回空字典
    """
    try:
        with DatabaseConnection() as db:
            # 查询指定ID的菜品
            query = """
            SELECT 
                id, dish_name, price, description, category, 
                spice_level, flavor, main_ingredients, cooking_method, 
                is_vegetarian, allergens, is_available
            FROM menu_items 
            WHERE id = %s AND is_available = 1
            """

            db.cursor.execute(query, (item_id,))
            result = db.cursor.fetchone()

            if not result:
                logger.warning(f"未找到ID为 {item_id} 的菜品")
                return {}

            # 辣度等级转换
            spice_levels = {0: "不辣", 1: "微辣", 2: "中辣", 3: "重辣"}
            spice_text = spice_levels.get(result['spice_level'], "未知")

            # 格式化菜品信息
            item = {
                "id": result['id'],
                "dish_name": result['dish_name'],
                "price": float(result['price']),
                "formatted_price": f"¥{result['price']:.2f}",
                "description": result['description'] or "暂无描述",
                "category": result['category'],
                "spice_level": result['spice_level'],
                "spice_text": spice_text,
                "flavor": result['flavor'] or "暂无描述",
                "main_ingredients": result['main_ingredients'] or "暂无",
                "cooking_method": result['cooking_method'] or "暂无",
                "is_vegetarian": bool(result['is_vegetarian']),
                "vegetarian_text": "是" if result['is_vegetarian'] else "否",
                "allergens": result['allergens'] if result['allergens'] and result['allergens'].strip() else "无",
                "is_available": bool(result['is_available'])
            }

            logger.info(f"成功查询到菜品: {item['dish_name']}")
            return item

    except Exception as e:
        logger.error(f"查询菜品ID {item_id} 失败: {e}")
        return {}

# 测试数据库连接
def connect_database() -> dict:
    """测试数据"""
    try:
        with DatabaseConnection() as db:
            # 获取游标
            db.cursor.execute("select 2 * 3")
            result = db.cursor.fetchone()
            if not result:
                logger.error("没数据")
                return {}
            else:
                return result
    except Exception as error:
        logger.error(f"数据库连接不可用: {str(error)}")
        return {}


if __name__ == "__main__":
    # 测试数据库功能
    print("=" * 60)
    print("数据库查询工具测试")
    print("=" * 60)

    # 测试连接
    print("\n1. 测试数据库连接...")
    if connect_database():
        print("✅ 数据库连接成功")

        # 测试查询所有菜品（字符串格式）
        print("\n2. 查询所有菜品信息（字符串格式）...")
        all_menu = get_all_menu_items()

        if all_menu and not all_menu.startswith("当前没有") and not all_menu.startswith("查询失败"):
            # 计算菜品数量
            menu_count = len(all_menu.split('\n'))
            print(f"查询结果: 共 {menu_count} 个菜品")

            # 显示前200个字符作为预览
            preview = all_menu[:200] + "..." if len(all_menu) > 200 else all_menu
            print(f"\n预览前200字符:")
            print(preview)

            print(f"\n完整字符串长度: {len(all_menu)} 字符")
        else:
            print(f"查询结果: {all_menu}")

        # 测试查询所有菜品（结构化列表）
        print("\n3. 查询所有菜品信息（结构化列表）...")
        menu_list = get_menu_items_list()

        if menu_list:
            print(f"✅ 成功获取结构化菜品列表")
            print(f"📊 菜品数量: {len(menu_list)}")

            # 显示第一个菜品的完整信息
            if len(menu_list) > 0:
                first_item = menu_list[0]
                print(f"\n📋 第一个菜品详情:")
                print(f"   - ID: {first_item['id']}")
                print(f"   - 名称: {first_item['dish_name']}")
                print(f"   - 价格: {first_item['formatted_price']}")
                print(f"   - 分类: {first_item['category']}")
                print(f"   - 辣度: {first_item['spice_text']}")
                print(f"   - 素食: {first_item['vegetarian_text']}")
        else:
            print("❌ 未获取到结构化菜品数据")

        # 测试按分类查询
        print("\n4. 按分类查询菜品...")
        categories = get_menu_items_list_by_category()

        if categories:
            print(f"✅ 成功获取分类数据")
            print(f"📁 分类数量: {len(categories)}")

            for category, items in categories.items():
                print(f"   - {category}: {len(items)} 个菜品")
        else:
            print("❌ 未获取到分类数据")

        # 测试单个菜品查询
        print("\n5. 测试单个菜品查询...")
        if menu_list and len(menu_list) > 0:
            test_id = menu_list[0]['id']
            single_item = get_menu_item_by_id(test_id)

            if single_item:
                print(f"✅ 成功查询单个菜品")
                print(f"📋 菜品信息: {single_item['dish_name']} - {single_item['formatted_price']}")
            else:
                print(f"❌ 查询菜品ID {test_id} 失败")
        else:
            print("⚠️  跳过单个菜品查询测试（无可用菜品）")

    else:
        print("❌ 数据库连接失败，请检查配置")

    print("\n" + "=" * 60)
    print("✅ 数据库工具测试完成")
    print("=" * 60)
